using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace SDG.UnitTesting.SimpleTalk
{
    public static class DataAccess
    {
        public static DataTable GetAllCustomers()
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Customers", sqlConn);
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);

            DataTable dt = new DataTable();
            sqlDA.Fill(dt);

            return dt;
        }

        public static DataTable GetCustomerByID(string customerID)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Customers WHERE CustomerID = @customerID", sqlConn);
            sqlCmd.Parameters.Add("@customerID", SqlDbType.NVarChar).Value = customerID;
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);

            DataTable dt = new DataTable();
            sqlDA.Fill(dt);

            return dt;
        }

        public static DataTable GetAllEmployees()
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Employees", sqlConn);
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);

            DataTable dt = new DataTable();
            sqlDA.Fill(dt);

            return dt;
        }

        public static DataTable GetEmployeeByID(int employeeID)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
            SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Employees WHERE EmployeeID = @employeeID", sqlConn);
            sqlCmd.Parameters.Add("@employeeID", SqlDbType.NVarChar).Value = employeeID;
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);

            DataTable dt = new DataTable();
            sqlDA.Fill(dt);

            return dt;
        }
    }
}
